IF OBJECT_ID('ssf.dpnd_merge_parsed_with_derived_object_header') IS NOT NULL
	DROP FUNCTION ssf.dpnd_merge_parsed_with_derived_object_header
GO

CREATE FUNCTION ssf.dpnd_merge_parsed_with_derived_object_header (
	@parsed XML (ssf.object_header)
	, @derived XML (ssf.object_header)
)
RETURNS XML (ssf.object_header)
AS
BEGIN

DECLARE	@merged_parameter XML
			, @merged_input XML
			, @merged XML (ssf.object_header)

SET @merged = NULL

SET @merged_parameter = (
	SELECT d.parameter_order
		, d.parameter_name
		, p.parameter_desc
		, d.data_type
		, p.default_value
		, d.return_type
	FROM (
		SELECT parameter_dependency.value('(/parameter_dependency/parameter_order)[1]', 'SMALLINT') AS parameter_order
			, parameter_dependency.value('(/parameter_dependency/parameter_name)[1]', 'NVARCHAR(128)') AS parameter_name
			, parameter_dependency.value('(/parameter_dependency/parameter_desc)[1]', 'NVARCHAR(2048)') AS parameter_desc
			, parameter_dependency.value('(/parameter_dependency/data_type)[1]', 'NVARCHAR(128)') AS data_type
			, parameter_dependency.value('(/parameter_dependency/default_value)[1]', 'NVARCHAR(512)') AS default_value
			, parameter_dependency.value('(/parameter_dependency/return_type)[1]', 'VARCHAR(16)') AS return_type
		FROM (
			SELECT t.c.query('.') AS parameter_dependency FROM @derived.nodes('/object_header/parameter_dependency') AS t(c)
		) x
	) d
	LEFT JOIN (
		SELECT parameter_dependency.value('(/parameter_dependency/parameter_name)[1]', 'NVARCHAR(128)') AS parameter_name
			, parameter_dependency.value('(/parameter_dependency/parameter_desc)[1]', 'NVARCHAR(2048)') AS parameter_desc
			, parameter_dependency.value('(/parameter_dependency/data_type)[1]', 'NVARCHAR(128)') AS data_type
			, parameter_dependency.value('(/parameter_dependency/default_value)[1]', 'NVARCHAR(512)') AS default_value
			, parameter_dependency.value('(/parameter_dependency/return_type)[1]', 'VARCHAR(16)') AS return_type
		FROM (
			SELECT t.c.query('.') AS parameter_dependency FROM @parsed.nodes('/object_header/parameter_dependency') AS t(c)
		) x
	) p
	ON d.parameter_name = p.parameter_name
	FOR XML RAW ('parameter_dependency'), ELEMENTS, TYPE
)

DECLARE @input_dependency TABLE (
	dpnd_object_name NVARCHAR(512)
	, object_type NVARCHAR(128)
)

;
WITH parsed_input
AS (
	SELECT input_dependency.value('(/input_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
		, input_dependency.value('(/input_dependency/object_type)[1]', 'NVARCHAR(128)') AS object_type
	FROM (
		SELECT t.c.query('.') AS input_dependency FROM @parsed.nodes('/object_header/input_dependency') AS t(c)
	) x
)
, parsed_object_list
AS (
	SELECT DISTINCT dependency.value('(/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
	FROM (
		SELECT t.c.query('.') AS dependency FROM @parsed.nodes('/object_header/input_dependency/dpnd_object_name') AS t(c)
		UNION ALL
		SELECT t.c.query('.') AS dependency FROM @parsed.nodes('/object_header/temporary_dependency/dpnd_object_name') AS t(c)
		UNION ALL
		SELECT t.c.query('.') AS dependency FROM @parsed.nodes('/object_header/output_dependency/dpnd_object_name') AS t(c)
	) x
)
, derived_input
AS (
	SELECT input_dependency.value('(/input_dependency/dpnd_object_name)[1]', 'NVARCHAR(512)') AS dpnd_object_name
		, input_dependency.value('(/input_dependency/object_type)[1]', 'NVARCHAR(128)') AS object_type
	FROM (
		SELECT t.c.query('.') AS input_dependency FROM @derived.nodes('/object_header/input_dependency') AS t(c)
	) x
)
INSERT @input_dependency
SELECT *
FROM parsed_input
UNION
SELECT d.*
FROM derived_input d
WHERE d.dpnd_object_name NOT IN (SELECT dpnd_object_name FROM parsed_object_list)

SET @merged_input = (
	SELECT object_type
		, dpnd_object_name
	FROM @input_dependency
	FOR XML RAW ('input_dependency'), ELEMENTS, TYPE
)

SET @merged = (
	SELECT @parsed.query('(/object_header/object_summary)[1]')
		, @merged_parameter
		, @merged_input
		, @parsed.query('/object_header/temporary_dependency')
		, @parsed.query('/object_header/output_dependency')
		, @parsed.query('/object_header/object_comment')
	FOR XML RAW ('object_header'), ELEMENTS, TYPE
)

RETURN @merged

END
GO
